package com.winning.sx.microframework.dao.base_entity;
import com.winning.dapi.data.api.IDataAccessService;
import com.winning.dapi.data.entity.DataTable;
import com.winning.sx.microframework.common.util.Constains;
import com.winning.sx.microframework.common.util.SqlTool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.Map;

@Repository
public class ImpDao implements IDao {
    @Autowired

    protected IDataAccessService iDataAccessService;
    final String SYS_VALIDFLAG_TRUE = "100001";
    final String SYS_VALIDFLAG_FALSE = "100002";
    protected String TABLE_NAMNE = "";

    @Override
    public DataTable queryList(Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where validFlag= '" + SYS_VALIDFLAG_TRUE + "' ";
            if (param != null) {
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }
                }
            }
            sql += " order by createTime";
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    @Override
    public Map queryEntity(Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where validFlag= '" + SYS_VALIDFLAG_TRUE + "'";
            for (String key : param.keySet()) {
                if (!"".equals(param.get(key)))
                    if (!"".equals(param.get(key)))
                     sql += " and " + key + "='" + param.get(key) + "'";
            }
            table = iDataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return null;
            } else if (table.getTotalRowNum() > 1) {
                throw new SQLException("数据库中存在多行被查询数据！");
            } else {
                return table.getDataRows().get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }

    @Override
    public boolean isExit(Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where 1=1 ";
            for (String key : param.keySet()) {
                if ("id".equals(key)) {
                    sql += (" and " + key + "!='" + param.get(key) + "'");
                } else
                    sql += (" and " + key + "='" + param.get(key) + "'");
            }
            table = iDataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }

    @Override
    public boolean insert(Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行insert");
            }
            if (param.get("id") != null && !"".equals(param.get("id"))) {
                throw new SQLException("传入的数据带有主键，请使用update操作此数据！");
            }
            String sql = "set nocount on;" + "insert into " + TABLE_NAMNE + " ";
            String keys = "", values = "";
            for (String key : param.keySet()) {
                if (!"id".equals(key))
                    keys += ("," + key);
            }
            for (String key : param.keySet()) {
                if (!"id".equals(key))
                    values += (",'" + param.get(key) + "'");
            }
            sql += ("(" + keys.substring(1) + ",validFlag,createTime) values (" + values.substring(1) + ",'" + Constains.SYS_VALIDFLAG_TRUE + "'," + SqlTool.getSysTime() + ");" + "SELECT SCOPE_IDENTITY() as id;");
            DataTable dataTable = iDataAccessService.querySql(sql);
            String id = dataTable.getDataRows().get(0).get("id").toString();
            param.put("id", id);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("插入信息失败！");
        }
    }

    @Override
    public boolean update(Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set ";
            String updateSet = "";
            for (String key : param.keySet()) {
                if (!"id".equals(key))
                    updateSet += ("," + key + "='" + param.get(key) + "'");
            }
            if(updateSet.length()==0) return true;
            sql += (updateSet.substring(1) + ",updateTime=" + SqlTool.getSysTime() + " where id=" + param.get("id"));
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新信息失败！");
        }
    }


    @Override
    public boolean delete(Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set validFlag='" + SYS_VALIDFLAG_FALSE + "' ,updateTime=" + SqlTool.getSysTime() + " where id=" + String.valueOf(param.get("id"));
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("删除信息失败！");
        }
    }

    @Override
    public DataTable queryView(String viewName, Map<String, String> param, String orderBy) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + viewName;
                if (param != null) {
                sql += " where 1=1 ";
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }

                }
            }
            if (orderBy != null && !"".equals(orderBy))
                sql += " order by " + orderBy;
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }
/*
 querySql(
 "analysis_trend_insurance",
 "time,sum(buyCount) buyCount,sum(buyAmtSch) buyAmtSch,sum(buyAmtStu) buyAmtStu,sum(payCount) payCount,sum(payAmt) payAmt",
 param,
 "time",
 "");
  */
    public DataTable querySql(String veiwName,String columns, Map<String, String> param, String orderBy,String groupBy) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT "+columns+" from " + veiwName;
            if (param != null) {
                sql += " where 1=1 ";
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }

                }
            }
            if (groupBy != null && !"".equals(groupBy))
                sql += " group by " + groupBy;
            if (orderBy != null && !"".equals(orderBy))
                sql += " order by " + orderBy;
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public DataTable querySql(String veiwName,String columns, Map<String, String> param,String where, String orderBy,String groupBy) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT "+columns+" from " + veiwName;
            if (param != null) {
                sql += " where 1=1 ";
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }

                }
            }
            if (where != null && !"".equals(where))
                sql += where;
            if (groupBy != null && !"".equals(groupBy))
                sql += " group by " + groupBy;
            if (orderBy != null && !"".equals(orderBy))
                sql += " order by " + orderBy;
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }
}
